package com.wuseguang.springcloud.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.wuseguang.springcloud.JdbcTemplate.WXJdbc;
import com.wuseguang.springcloud.dao.jdbc.StudentVacationJdbc;
import com.wuseguang.springcloud.service.StudentVacationService;
import com.wuseguang.springcloud.util.*;
import com.wuseguang.springcloud.util.WeChat.TemplateTools;
import com.wuseguang.springcloud.util.WeChat.WXUtil;
import org.apache.commons.codec.binary.Base64;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class StudentVacationServiceImpl implements StudentVacationService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private StudentVacationJdbc vacationJdbc;

    @Autowired
    private WXJdbc wXJdbc;

    /**
     * @描述 根据学生id查询学生请假信息情况
     * @创建人 zm
     * @创建时间 2020/3/26
     */
    @Override
    public List<Map<String, Object>> queryStudentVacationById(Integer id) {
        String sql = "SELECT s.student_name name, CONCAT('提交了',IF(TYPE=0,'病假','事假')) title,s.head_portrait,v.id,v.student_id,v.type,v.school_id,DATE_FORMAT(v.stime,'%Y-%m-%d %H:%i') stime,DATE_FORMAT(v.etime,'%Y-%m-%d %H:%i') etime,v.state,v.content,DATE_FORMAT(v.opertime ,'%Y-%m-%d  %H:%i') opertime ,c.name classname,t.id tid,t.name tname FROM vacation v,student s,classes c LEFT JOIN  teacher t ON c.class_teacher_id=t.id  WHERE s.id=v.student_id AND c.id=s.class_id AND v.student_id=? ORDER BY v.opertime DESC";
        return vacationJdbc.queryListMap(sql, id);
    }


    @Override//0 待审核 (1第一审核通过 2通过) 3第一审核人拒绝 4销假中 5已销假 6第二审核人未通过
    public Result updateStudentVacationState(Integer id, Integer state) {
        String sql = "UPDATE vacation SET state=? WHERE id=? ";
        int i = vacationJdbc.updateByParameter(sql, state, id);
        if (i > 0) {
            return new Result();
        }
        return new Result(CodeUtil.MODIFICATION_FAILED);
    }

    /**
     * @描述 根据班级id查班主任信息
     * @创建人 zm
     * @创建时间 2020/3/27
     */
    @Override
    public Map<String, Object> queryHeadMaster(Integer classId) {
        String sql = "SELECT  t.id tid,t.name tname ,t.head_portrait FROM  classes c , teacher t WHERE c.class_teacher_id=t.id AND c.id=?";
        Map<String, Object> map = new HashMap<>();
        try {
            map = vacationJdbc.queryMap(sql, classId);
        } catch (Exception e) {
            logger.info("queryHeadMaster(==>,error={}", e);
        }
        return map;
    }

    /**
     * @描述 撤回请假
     * @创建人 zm
     * @创建时间 2020/3/26
     */
    @Override
    public long delStudentVacation(Integer id) {
        String sql = "delete from vacation where id=?";
        return vacationJdbc.updateByParameter(sql, id);
    }

    /**
     * @描述 添加请假信息
     * @创建人 zm
     * @创建时间 2020/3/27
     */
    @Override
    public long addStudentVacation(Integer id, Integer school_id, String stime, String etime, String content, Integer type) {
            String sql = "insert into `vacation` (`student_id`, `type`, `school_id`, `stime`, `etime`, `content`, `opertime`) values(?,?,?,?,?,?,now())";
        int i = vacationJdbc.updateByParameter(sql, id, type, school_id, stime, etime, content);
        if (i > 0) {
            officialAccountPush(id, content);
        }
        return i;
    }


    /**
     * 公众号推送
     *
     * @param id
     */
    public void officialAccountPush(Integer id, String content) {

        //获取token
        try {
            String accessToken = wXJdbc.getOfficialAccountAccessToken(0);

            //查询家长的小程序openId
            //String sql = "SELECT c.name cname,s.student_name sname,t.open_id_two FROM   classes c, student s, teacher t  WHERE c.class_teacher_id=t.id AND c.id=s.class_id AND s.id=? ";

            String sql="SELECT c.name cname,u.student_name sname,t.open_id_two STRING FROM  school_vacation s,teacher_role r,classes c , " +
                    "student u,teacher t WHERE s.one_roles_id=r.role_id  AND r.type=3 AND u.class_id=c.id AND t.id=r.teacher_id " +
                    " AND s.school_id =r.school_id  AND r.pid=c.id AND c.graduation=0 AND u.id=? LIMIT 1";

            //家长的openId
            String openId = null;
            String cname = null;//班级名字
            String sname = null;//学生名字

            //家长openid
            Map<String, Object> parent = vacationJdbc.queryMap(sql, id);

            openId = (String) parent.get("open_id_two");
            cname = (String) parent.get("cname");
            sname = (String) parent.get("sname");

            //判断openId是否为空
            if (!StringUtils.isEmpty(openId)) {
                Map<String, Object> pushMap = new HashMap<>();

                pushMap.put("first", "尊敬的老师您好，以下是" + sname + "同学的请假申请");
                pushMap.put("keyword1", DateUtil.getTimeAndMinutes());
                pushMap.put("keyword2", cname);
                pushMap.put("keyword3", sname);
                pushMap.put("keyword4", content);
                pushMap.put("remark", "老师您好，" + sname + "提交了请假申请，请尽快批复。");

                //跳转小程序信息
                JSONObject appletJson = new JSONObject();
                appletJson.put("appid", "wxeef24208ca48a4c2");
                appletJson.put("pagepath", "pages/leave/index");

                //解析后的数据
                JSONObject json = TemplateTools.initializationData(pushMap);
                //推送公众号信息
                WXUtil.officialAccountPush(accessToken, openId, "JkQoLjd4_p8uMEitDTL-qzQPIwU3Uebk3SPGl92B5Nw", "", appletJson, json);
            }
        } catch (Exception e) {

        }
    }


    /**
     * @描述 查该班级所有通知
     * @创建人 zm
     * @创建时间 2020/4/1
     */
    @Override
    public Result queryClassNotice(Integer classId, Integer indexNo, Integer rows) {
        String sql = "SELECT s.id,DATE_FORMAT(s.opertime,'%Y-%m-%d %H:%i')  opertime,s.content,s.url,s.teacher_id,t.name,t.head_portrait,s.annex,s.website FROM class_notice c,notice s LEFT JOIN teacher t ON s.teacher_id=t.id  WHERE c.notice_id=s.id AND c.class_id=? ORDER BY s.opertime DESC";
        List<Map<String, Object>> list = vacationJdbc.queryListMap(sql, classId);

        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        //返回数据
        return new Result(new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo));
    }


    /**
     * @描述 按班级查所有图片
     * @创建人 zm
     * @创建时间 2020/4/1
     */
    @Override
    public Result queryClassPicturesAll(Integer classId, Integer indexNo, Integer rows) {
        String sql = "SELECT e.class_icon,e.name class_name,s.id,s.name picture_name,s.pictyres_url,DATE_FORMAT(s.opertime,'%Y-%m-%d %H:%i')  opertime,s.teacher_id,t.name,t.head_portrait FROM class_pictures c ,classes e,pictures s LEFT JOIN teacher t ON s.teacher_id=t.id  " +
                "WHERE c.class_id=? AND s.checks=0 AND s.display=1 AND c.pictures_id=s.id AND c.class_id=e.id  AND c.school_id=s.school_id " +
                " ORDER BY s.opertime DESC";
        List<Map<String, Object>> list = vacationJdbc.queryListMap(sql, classId);

        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        PaginationUtil<?> paginationUtil = new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo);
        //返回数据
        return new Result(paginationUtil);
    }


    /**
     * @描述 根据班级查所有视频
     * @创建人 zm
     * @创建时间 2020/4/1
     */
    @Override
    public Result queryClassVideosAll(Integer classId, Integer indexNo, Integer rows) {
        String sql = "SELECT s.id,s.video_name ,s.video_url,DATE_FORMAT(s.opertime,'%Y-%m-%d %H:%i')  opertime,s.video_cover,s.teacher_id,t.name,t.head_portrait FROM class_video c ,video s LEFT JOIN teacher t ON s.teacher_id=t.id WHERE c.class_id=? AND s.checks=0 AND s.display=1 AND c.video_id=s.id  AND c.school_id=s.school_id " +
                " ORDER BY c.top DESC,s.opertime DESC";
        List<Map<String, Object>> list = vacationJdbc.queryListMap(sql, classId);
        //创建分页工具
        PagingTool tool = new PagingTool();
        //放入页码
        tool.setIndexNo(indexNo);
        //放入每页显示数量
        tool.setRows(rows);
        //放入角色集合
        tool.setList(list);
        PaginationUtil<?> paginationUtil = new PaginationUtil<>(list.size(), rows, tool.getList(), indexNo);
        //返回数据
        return new Result(paginationUtil);
    }


    @Override
    public Result queryStudentAchievement(Integer id) {//学生id
        String sql = "SELECT t.type,h.id tid,h.name tname,h.head_portrait,t.id,CONCAT(s.student_name,'-',t.achievement_name) aname,DATE_FORMAT(t.sendtime,'%Y-%m-%d %H:%i') opertime  FROM  school_achievement_student s,school_achievement t LEFT JOIN teacher h  ON t.teacher_id=h.id  WHERE s.achievement_id=t.id AND t.state=1 AND s.student_id=? ORDER BY t.opertime DESC,t.id DESC";
        List<Map<String, Object>> maps = vacationJdbc.queryListMap(sql, id);
        sql = "SELECT c.id,a.fraction,c.course_name,c.total_score FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=?       " +
                "       UNION ALL  ( SELECT '0',SUM(a.fraction+0) fraction,'总分' course_name, SUM(c.total_score+0) total_score FROM    school_achievement_student s  LEFT JOIN   " +
                " school_achievement_course c  ON s.achievement_id=c.achievement_id  LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id) " +
                "   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=?  ) ";
        String sql1 = "SELECT c.id,a.fraction,c.course_name,c.total_score FROM    school_achievement_student s  LEFT JOIN  school_achievement_course c  ON s.achievement_id=c.achievement_id " +
                " LEFT JOIN student_achievement a ON(c.id=a.achievement_course_id AND s.student_id=a.student_id)   WHERE c.state=1 AND s.achievement_id=? AND s.student_id=?       ";
        if (maps != null && maps.size() > 0) {
            for (int i = 0; i < maps.size(); i++) {
                Map<String, Object> map = maps.get(i);
                if ("0".equals(map.get("type").toString())) {
                    map.put("list", vacationJdbc.queryListMap(sql, map.get("id"), id, map.get("id"), id));
                } else {
                    map.put("list", vacationJdbc.queryListMap(sql1, map.get("id"), id));
                }
            }
        }
        return new Result(maps);
    }

}
